﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.IO;
using System.Collections;
using System.Runtime.InteropServices;
using System.Configuration;
using FirebirdSql.Data.FirebirdClient;
using System.Data;

namespace TRPOP.ComSqlDAL.FBSqlAssistant
{
    #region  sql 操作方式： Insert  Update Filter
    /// <summary>
    /// sql 操作方式： Insert  Update Filter
    /// </summary>
    public enum ParamType
    {
        /// <summary>
        /// Insert
        /// </summary>
        Insert,
        /// <summary>
        /// Update
        /// </summary>
        Update,
        /// <summary>
        /// Filter
        /// </summary>
        Filter
    }
    #endregion

    /// <summary>
    /// Firebird 辅助操作方法
    /// </summary>
    public class SQLAssistant
    {
        #region Create DB
        /// <summary>
        /// 创建数据库
        /// </summary>
        /// <param name="strDBFile"></param>
        /// <param name="strUserID"></param>
        /// <param name="strPassword"></param>
        /// <returns></returns>
        public static bool CreateDB(string FireBirdDBFileConnection)
        {
            try
            {
                FbConnection.CreateDatabase(FireBirdDBFileConnection);
                return true;
            }
            catch
            {
                return false;
            }
        }
        #endregion

        #region INI SQL操作

        /// <summary>
        /// 执行指定ini文件、制定sql标记 里的sql
        /// </summary>
        /// <param name="iniPath"></param>
        /// <param name="queryName"></param>
        /// <param name="strConnectionstring"></param>
        /// <returns></returns>
        public static DataSet ExecuteIni(string iniPath, string queryName, string strConnectionstring)
        {
            IniFile file = new IniFile(iniPath);
            string strSQL = file.GetKey("Query", queryName);
            return SQLAssistant.Execute(strSQL, strConnectionstring);
        }
        /// <summary>
        /// 获取指定ini文件里对应的标签内容
        /// </summary>
        /// <param name="iniPath"></param>
        /// <param name="queryName"></param>
        /// <returns></returns>
        public static string GetIniQuery(string iniPath, string queryName)
        {
            IniFile f = new IniFile(iniPath);

            return f.GetKey("Query", queryName);
        }
        #endregion

        #region 执行sql语句
        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="strQuery"></param>
        /// <param name="strConnectionstring"></param>
        /// <returns></returns>
        public static int ExecuteQuery(string strQuery, string strConnectionstring)
        {
            int intQuery = 0;
            FbCommand cmd = new FbCommand(strQuery);
            cmd.CommandType = CommandType.Text;
            using (cmd.Connection = new FbConnection(strConnectionstring))
            {
                cmd.Connection.Open();
                intQuery = cmd.ExecuteNonQuery();
            }
            return intQuery;
        }
        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="strConnectionstring"></param>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public static DataSet Execute(string commandText, string strConnectionstring)
        {
            FbDataAdapter da = new FbDataAdapter(commandText, strConnectionstring);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;
        }

        #endregion

        #region Select 快捷操作

        /// <summary>
        /// 查询指定表里的所有数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="strConnectionstring"></param>
        /// <returns></returns>
        public static DataSet SelectAll(string tableName, string strConnectionstring)
        {
            return Select(tableName, strConnectionstring);
            //return SQLAssistant.Execute("SELECT * FROM " + SQLHelp.DQuote(tableName), strConnectionstring);
        }

        /// <summary>
        /// 检查指定的表里是否有数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="strConnectionstring"></param>
        /// <param name="filterColVals"></param>
        /// <returns></returns>
        public static bool Exists(string tableName, string strConnectionstring, params object[] filterColVals)
        {
            DataTable table = Select(tableName, strConnectionstring, filterColVals).Tables[0];

            return table.Rows.Count > 0;
        }

        /// <summary>
        /// 查询表数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="strConnectionstring"></param>
        /// <param name="filterColVals"></param>
        /// <returns></returns>
        public static DataSet Select(string tableName, string strConnectionstring, params object[] filterColVals)
        {
            return SQLAssistant.Execute("SELECT * FROM " + SQLHelp.TableColumnQuote(tableName) + SQLHelp.ParamList(ParamType.Filter, filterColVals), strConnectionstring);
        }
        #endregion

        #region Insert 快捷操作
        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="strConnectionstring"></param>
        /// <param name="colVals"></param>
        /// <returns></returns>
        public static int Insert(string tableName, string strConnectionstring, params object[] colVals)
        {
            return ExecuteQuery("INSERT INTO" + SQLHelp.TableColumnQuote(tableName) + SQLHelp.ParamList(ParamType.Insert, colVals), strConnectionstring);
        }

        /// <summary>
        /// 获取数据总数
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="strConnectionstring"></param>
        /// <returns></returns>
        public static int GetCount(string tableName, string strConnectionstring)
        {
            DataTable t = SQLAssistant.Execute("SELECT COUNT(*) FROM" + SQLHelp.TableColumnQuote(tableName), strConnectionstring).Tables[0];
            return Convert.ToInt32(t.Rows[0][0]);
        }

        #endregion

        #region Update 快捷操作

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="strConnectionstring"></param>
        /// <param name="ColValues">列</param>
        /// <param name="filterColVals">值</param>
        /// <returns></returns>
        public static int Update(string tableName, string strConnectionstring, object[] ColValues, params object[] filterColVals)
        {
            return ExecuteQuery("UPDATE" + SQLHelp.TableColumnQuote(tableName) + SQLHelp.ParamList(ParamType.Update, ColValues) + SQLHelp.ParamList(ParamType.Filter, filterColVals), strConnectionstring);
        }

        #endregion

        #region Delete
        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="strConnectionstring"></param>
        /// <param name="filterColVals"></param>
        /// <returns></returns>
        public static int Delete(string tableName, string strConnectionstring, params object[] filterColVals)
        {
            return ExecuteQuery("DELETE FROM" + SQLHelp.TableColumnQuote(tableName) + SQLHelp.ParamList(ParamType.Filter, filterColVals), strConnectionstring);
        }
        #endregion

        #region Stored Procedures 代码暂注释掉
        /*
        #region Methods

        // adds and returns a stored procedure out parameter ReturnValue
        public static int ExecuteNonQuery(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
        {
            int result;

            FbCommand command = BuildIntCommand(storedProcName, parameters);

            command.CommandType = CommandType.StoredProcedure;

            rowsAffected = command.ExecuteNonQuery();

            result = (int)(command.Parameters["ReturnValue"].Value);

            return result;
        }

        // return rows affected
        public static int ExecuteNonQuery(string storedProcName, IDataParameter[] parameters)
        {
            FbCommand command = BuildIntCommand(storedProcName, parameters);

            command.CommandType = CommandType.StoredProcedure;

            return command.ExecuteNonQuery();
        }

        public static FbDataReader ExecuteReader(string storedProcName, IDataParameter[] parameters)
        {
            FbDataReader dr;

            FbCommand command = BuildQueryCommand(storedProcName, parameters);
            command.CommandType = CommandType.StoredProcedure;

            dr = command.ExecuteReader();

            return dr;
        }

        public static DataSet ExecuteDataSet(string storedProcName, IDataParameter[] parameters)
        {
            DataSet ds = new DataSet();

            FbDataAdapter da = new FbDataAdapter();
            da.SelectCommand = BuildQueryCommand(storedProcName, parameters);
            da.Fill(ds);

            return ds;
        }

        #endregion

        #region Private Helpers

        private static FbCommand BuildIntCommand(string storedProcName, IDataParameter[] parameters)
        {
            FbCommand command = BuildQueryCommand(storedProcName, parameters);

            command.Parameters.Add(new FbParameter(
                "ReturnValue",
                FbDbType.Integer,
                4, // Size 
                ParameterDirection.ReturnValue,
                false, // is nullable 
                0, // byte precision 
                0, // byte scale 
                string.Empty,
                DataRowVersion.Default,
                ""));

            return command;
        }

        private static FbCommand BuildQueryCommand(string storedProcName, IDataParameter[] parameters)
        {
            FbCommand command = new FbCommand(storedProcName, new FbConnection(SQLHelp.Connectionstring));

            command.CommandType = CommandType.StoredProcedure;

            if (parameters != null)
            {
                foreach (FbParameter parameter in parameters)
                {
                    command.Parameters.Add(parameter);
                }
            }

            return command;
        }

        endregion
        */
        #endregion
    }

    /// <summary>
    /// IniFile Class
    /// </summary>
    public class IniFile
    {
        /// <summary>
        /// 路径
        /// </summary>
        public string Path;

        #region Win32
        [DllImport("kernel32")]
        private static extern long WritePrivateProfileString(string section,
            string key, string val, string filePath);

        [DllImport("kernel32")]
        private static extern int GetPrivateProfileString(string section,
                 string key, string def, StringBuilder retVal,
            int size, string filePath);
        #endregion

        /// <summary>
        /// Constructors
        /// </summary>
        /// <param name="path"></param>
        public IniFile(string path)
        {
            Path = path;
        }

        /// <summary>
        /// Set Key
        /// </summary>
        /// <param name="section"></param>
        /// <param name="key"></param>
        /// <param name="value"></param>
        public void SetKey(string section, string key, string value)
        {
            WritePrivateProfileString(section, key, value, this.Path);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="section"></param>
        /// <param name="key"></param>
        /// <returns></returns>
        public string GetKey(string section, string key)
        {
            StringBuilder temp = new StringBuilder(5000);

            int i = GetPrivateProfileString(section, key, "", temp, 5000, this.Path);

            return temp.ToString();
        }
    }

    /// <summary>
    /// SQLHelp Class
    /// </summary>
    public class SQLHelp
    {
        #region string Helpers

        /// <summary>
        /// sql值处理
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        public static string ValueQuote(string s)
        {
            if (s == null)
            {
                return " null ";
            }
            return " \'" + s + "\' ";
        }

        /// <summary>
        /// 表名 列名 处理
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        public static string TableColumnQuote(string s)
        {
            return " \"" + s + "\" ";
        }

        /// <summary>
        /// 加 ()
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        public static string Parenthesis(string s)
        {
            return "(" + s + ")";
        }

        /// <summary>
        /// 加空格
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        public static string Space(string s)
        {
            return " " + s + " ";
        }

        /// <summary>
        /// 移除后几位字符串
        /// </summary>
        /// <param name="s"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public static string RemoveLast(string s, int count)
        {
            return s.Remove(s.Length - count, count);
        }

        #endregion

        #region 动态拼接sql
        /// <summary>
        /// 动态拼接sql
        /// </summary>
        /// <param name="paramType"></param>
        /// <param name="colVals"></param>
        /// <returns></returns>
        public static string ParamList(ParamType paramType, object[] colVals)
        {
            return SQLHelp.ParamColl(paramType, colVals);
        }
        /// <summary>
        /// 动态拼接sql
        /// </summary>
        /// <param name="paramType"></param>
        /// <param name="colVals"></param>
        /// <returns></returns>
        public static string ParamColl(ParamType paramType, params object[] colVals)
        {
            string s = "";
            string f = "";
            string v = "";
            string fn = "";
            string fv = "";
            string op = "=";
            string suffix = "";

            if (colVals == null || colVals.Length < 2) // one col and one val is must!
            {
                return "";
            }

            switch (paramType)
            {
                case ParamType.Insert:
                    op = "";
                    suffix = ",";
                    break;

                case ParamType.Update:
                    s = " SET ";
                    suffix = ",";
                    break;

                case ParamType.Filter:
                    s = " WHERE ";
                    op = "=";
                    suffix = "AND";
                    break;
            }

            for (int i = 0; i < colVals.Length - 1; i = i + 2)
            {
                f = colVals.GetValue(i).ToString();
                if (colVals.GetValue(i + 1) == null)
                {
                    v = null;
                }
                else
                {
                    v = colVals.GetValue(i + 1).ToString().Replace("'", "''");
                }


                s += SQLHelp.TableColumnQuote(f);
                fn += SQLHelp.TableColumnQuote(f);
                fv += SQLHelp.ValueQuote(v);

                if (op != "")
                {
                    s += SQLHelp.Space(op) + SQLHelp.ValueQuote(v);
                }

                if (suffix != "")
                {
                    s += SQLHelp.Space(suffix);
                    fn += SQLHelp.Space(suffix);
                    fv += SQLHelp.Space(suffix);
                }
            }

            if (suffix != "")
            {
                s = SQLHelp.RemoveLast(s, (suffix.Length + 2)); // 2 is added because of speace we added above in suffix
                fn = SQLHelp.RemoveLast(fn, (suffix.Length + 2));
                fv = SQLHelp.RemoveLast(fv, (suffix.Length + 2));
            }

            switch (paramType)
            {
                case ParamType.Insert:
                    s = SQLHelp.Parenthesis(fn) + SQLHelp.Space("VALUES") + SQLHelp.Parenthesis(fv);
                    break;
            }

            return s;

        }

        #endregion
    }
}
